<?php 
	if(isset($_POST['submit']) && $_POST['submit']=='Clean Up') {
		$db2 = new CDatabase();
		$db2->DBConnect();
		$db3 = new CDatabase();
		$db3->DBConnect();
		$db4 = new CDatabase();
		$db4->DBConnect();
		$url = array();
		$patterns = array ('/www./', '/^http:\/\//');
		$replace = array ('', '');
		
		$db->DBQuery("SELECT * FROM contestant;");
		while($db->DBGetRow()) {
			if(substr_count($db->access['url'], 'http://') || substr_count($db->access['url'], 'www.')) {
				//if found a url that has a http:// or www. in front try to find an equivalent in the db without
				$oldcid = $db->access['id'];
				$newurl = preg_replace($patterns, $replace, $db->access['url']);
				$db2->DBQuery("SELECT * FROM contestant WHERE url = '".$newurl."';");
				if($db2->DBGetRow()) {
					//if equivalent found replace all reference id's to the old url to point to this url and delete the old url
					$newcid = $db2->access['id'];
					$db2->DBQuery("SELECT * FROM nomination WHERE contestant_id = '".$oldcid."';");
					while($db2->DBGetRow()) {
						$oldnid = $db2->access['id'];
						$old_nomcount = $db2->access['nomination_count'];
						$db3->DBQuery("SELECT * FROM nomination WHERE category_id = '".$db2->access['category_id']."' AND contestant_id = '".$newcid."';");
						if($db3->DBGetRow()) {
							//if there other nominations for this contestant in this category update the nomination id to new one in the nomination table and user_nomination table and delete the old one
							$newnid = $db3->access['id'];
							$db3->DBQuery("UPDATE nomination SET nomination_count = (nomination_count+".$old_nomcount.") WHERE id = '".$newnid."';");
							$db3->DBQuery("SELECT * FROM user_nomination WHERE nomination_id = '".$oldnid."';");
							while($db3->DBGetRow()) {
								$db4->DBQuery("SELECT * FROM user_nomination WHERE user_id = '".$db3->access['user_id']."' AND nomination_id = '".$newnid."';");
								if(!$db4->DBGetRow()) $db4->DBQuery("UPDATE user_nomination SET nomination_id = '".$newnid."' WHERE user_id = '".$db3->access['user_id']."' AND nomination_id = '".$oldnid."';");
							}
							$db3->DBQuery("DELETE FROM nomination WHERE id='".$oldnid."';");
						} else {
							//if no other nomination for this contestant in this categorie just update the contestant id to new one.
							$db3->DBQuery("UPDATE nomination SET contestant_id = '".$newcid."' WHERE id = '".$oldnid."';");
						}
					}
					$db2->DBQuery("DELETE FROM contestant WHERE id='".$oldcid."';");
				} else {
					//if no equivalent found just update existing url to cleaned format
					$db2->DBQuery("UPDATE contestant SET url = '".$newurl."' WHERE id = '".$oldcid."';");
				}
			}
		}
	}
?>
<h2 class='header'>Clean up Nominations</h2>
<div class='contentBody'>
	<?php
		if(isset($_POST['submit']) && $_POST['submit']=='Clean Up') 
			echo "<span class='success'>Thanks, Nominations are now Clean</span>";
	?>
	<p>Please Click the Clean Up button to clean up the nominations. This means merging duplicate blogs nominations and other such stuff.</p>
	<form action='update' method='post'>
		<input type='submit' name='submit' value='Clean Up' />
	</form>
	<br />
</div>